/*
MySQL Backup
Database: coderhub
Backup Time: 2021-08-07 17:28:00
*/

SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS `coderhub`.`permissions`;
DROP TABLE IF EXISTS `coderhub`.`role_ferm`;
DROP TABLE IF EXISTS `coderhub`.`roles`;
DROP TABLE IF EXISTS `coderhub`.`user_role`;
DROP TABLE IF EXISTS `coderhub`.`users`;
CREATE TABLE `permissions` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '权限名称',
  `pid` int NOT NULL DEFAULT '0' COMMENT '父权限id',
  `type` int NOT NULL COMMENT '权限类型  1: 菜单, 2: 按钮',
  `icon` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT 'icon类名，可选',
  `component` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `path` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `level` int DEFAULT NULL COMMENT '菜单等级，用以生成树结构',
  `permissionValue` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '菜单权限值，可选',
  `createTime` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updateTime` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `role_ferm` (
  `roleID` int NOT NULL,
  `permID` int NOT NULL,
  `createTime` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `undateTime` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`roleID`,`permID`),
  KEY `permID` (`permID`),
  CONSTRAINT `role_ferm_ibfk_1` FOREIGN KEY (`roleID`) REFERENCES `roles` (`id`),
  CONSTRAINT `role_ferm_ibfk_2` FOREIGN KEY (`permID`) REFERENCES `permissions` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `roles` (
  `id` int NOT NULL AUTO_INCREMENT,
  `roleName` varchar(10) NOT NULL,
  `remark` varchar(20) DEFAULT NULL,
  `delete` int DEFAULT '0',
  `createTime` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `undateTime` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `user_role` (
  `userID` bigint NOT NULL,
  `roleID` int NOT NULL,
  `createTime` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `undateTime` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`roleID`,`userID`) USING BTREE,
  KEY `user_role_ibfk_1` (`userID`),
  CONSTRAINT `user_role_ibfk_1` FOREIGN KEY (`userID`) REFERENCES `users` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `user_role_ibfk_2` FOREIGN KEY (`roleID`) REFERENCES `roles` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `users` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '用户id',
  `username` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '用户名',
  `password` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '密码',
  `phone` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '电话、手机号码',
  `createTime` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `undateTime` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `phone` (`phone`)
) ENGINE=InnoDB AUTO_INCREMENT=80050000000006 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
BEGIN;
LOCK TABLES `coderhub`.`permissions` WRITE;
DELETE FROM `coderhub`.`permissions`;
INSERT INTO `coderhub`.`permissions` (`id`,`name`,`pid`,`type`,`icon`,`component`,`path`,`level`,`permissionValue`,`createTime`,`updateTime`) VALUES (1, '系统管理', 0, 1, 'icon-lock', 'layout', '/aci', 1, NULL, '2021-08-01 15:52:12', '2021-08-07 11:45:44'),(2, '菜单管理', 1, 1, 'icon-menu', 'Menu', 'menu/list', 2, NULL, '2021-08-01 21:25:15', '2021-08-07 12:31:10'),(3, '用户管理', 1, 1, 'icon-icon-user', 'User', 'user/list', 2, NULL, '2021-08-01 21:25:51', '2021-08-07 12:31:13'),(4, '角色管理', 1, 1, 'icon-role', 'Role', 'role/list', 2, NULL, '2021-08-01 21:26:07', '2021-08-07 12:40:04');
UNLOCK TABLES;
COMMIT;
BEGIN;
LOCK TABLES `coderhub`.`role_ferm` WRITE;
DELETE FROM `coderhub`.`role_ferm`;
INSERT INTO `coderhub`.`role_ferm` (`roleID`,`permID`,`createTime`,`undateTime`) VALUES (4, 1, '2021-08-01 21:46:46', '2021-08-01 21:46:46'),(4, 2, '2021-08-01 21:46:46', '2021-08-01 21:46:46'),(4, 3, '2021-08-01 21:46:46', '2021-08-01 21:46:46'),(4, 4, '2021-08-01 21:46:46', '2021-08-01 21:46:46'),(5, 1, '2021-08-01 21:46:46', '2021-08-01 21:46:46'),(5, 2, '2021-08-01 21:46:46', '2021-08-01 21:46:46');
UNLOCK TABLES;
COMMIT;
BEGIN;
LOCK TABLES `coderhub`.`roles` WRITE;
DELETE FROM `coderhub`.`roles`;
INSERT INTO `coderhub`.`roles` (`id`,`roleName`,`remark`,`delete`,`createTime`,`undateTime`) VALUES (1, 'admin', 'boss', 0, '2021-08-01 10:05:31', '2021-08-01 22:18:39'),(4, 'admin1', '超级管理员', 0, '2021-08-01 12:14:35', '2021-08-01 12:14:35'),(5, 'admin2', '普通管理员', 0, '2021-08-01 12:16:20', '2021-08-01 21:46:05'),(6, 'admin3', '营业员', 0, '2021-08-01 12:19:52', '2021-08-01 21:46:16');
UNLOCK TABLES;
COMMIT;
BEGIN;
LOCK TABLES `coderhub`.`user_role` WRITE;
DELETE FROM `coderhub`.`user_role`;
INSERT INTO `coderhub`.`user_role` (`userID`,`roleID`,`createTime`,`undateTime`) VALUES (80050000000003, 1, '2021-08-01 16:13:43', '2021-08-01 16:13:43'),(80050000000004, 1, '2021-08-01 16:25:43', '2021-08-01 16:25:43'),(80050000000004, 4, '2021-08-01 17:01:45', '2021-08-01 22:23:42'),(80050000000003, 5, '2021-08-01 17:03:38', '2021-08-01 17:03:38');
UNLOCK TABLES;
COMMIT;
BEGIN;
LOCK TABLES `coderhub`.`users` WRITE;
DELETE FROM `coderhub`.`users`;
INSERT INTO `coderhub`.`users` (`id`,`username`,`password`,`phone`,`createTime`,`undateTime`) VALUES (80050000000003, 'student', 'e10adc3949ba59abbe56e057f20f883e', NULL, '2021-07-31 10:28:20', '2021-08-07 17:11:05'),(80050000000004, 'admin', 'e10adc3949ba59abbe56e057f20f883e', NULL, '2021-07-31 14:47:54', '2021-08-01 22:20:18');
UNLOCK TABLES;
COMMIT;
